package com.xingchen.loveparttimejob.dao.impl;

import com.xingchen.loveparttimejob.dao.JobInfoDAO;
import com.xingchen.loveparttimejob.model.pojo.JobInfo;
import com.xingchen.loveparttimejob.uitls.JDBCUtils;
import com.xingchen.loveparttimejob.uitls.PagerUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @author 小志
 * @date 2024/12/8 - 11:16
 */
public class JobInfoDAOImpl implements JobInfoDAO {
    @Override
    public Integer addJobRelease(JobInfo jobInfo, boolean closeDB) {
        String sql = "insert into t_jobInfo(ptt_id,ji_title,ji_describe,salary_min,salary_max,salary_type,job_address,job_environment,ji_recruited_people_number,ji_recruiting_people_number) output INSERTED.ji_id values(?,?,?,?,?,?,?,?,?,?)";
        JDBCUtils instance = JDBCUtils.getInstance();

        List<Object> list = new ArrayList<>();
        list.add(jobInfo.getPttId());
        list.add(jobInfo.getJiTitle());
        list.add(jobInfo.getJiDescribe());
        list.add(jobInfo.getSalaryMin());
        list.add(jobInfo.getSalaryMax());
        list.add(jobInfo.getSalaryType());
        list.add(jobInfo.getJobAddress());
        list.add(jobInfo.getJobEnvironment());
        list.add(jobInfo.getJiRecruitedPeopleNumber());
        list.add(jobInfo.getJiRecruitingPeopleNumber());


        Integer i = 0;
        try {
            i = instance.executeQueryCountOrId(sql, list, closeDB);
        } catch (SQLException e) {
            //事务为开启状态就回滚事务，并关闭连接
            if(instance.isOpenTransaction()){
                instance.rollbackTransaction(true);
            }
            throw new RuntimeException(e);
        }
        return i;
    }

    @Override
    public PagerUtil<JobInfo> getSelectJobInfoList(PagerUtil<JobInfo> pager,boolean closeDB) {

        //一个分页案例

        List<Object> parameters = new ArrayList<>();
        StringBuffer sql = new StringBuffer("select row_number() over(order by a.ji_id asc) as 序列,* from t_jobInfo as a where 1=1 ");

        if(pager.getSearch() != null && !Objects.equals(pager.getSearch(), "")){
            sql.append("and ji_title like ?");
            parameters.add(pager.getSearch());
        }

        sql.append("order by ji_id OFFSET (?-1)*? rows fetch next ? rows only");
        parameters.add(pager.getIndexPage());
        parameters.add(pager.getTotalRows());
        parameters.add(pager.getTotalRows());

        try {
            ArrayList<JobInfo> objects = JDBCUtils.getInstance().executeQuery(String.valueOf(sql), parameters, new JobInfo().getMapping(), JobInfo.class, true);
            pager.setData(objects);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return pager;
    }

    @Override
    public JobInfo getJobInfo(int id,boolean closeDB) {
        String sql = "select * from t_jobInfo where ji_id = ?";
        List<Object> parameters = new ArrayList<>();
        parameters.add(id);
        JobInfo jobInfo = new JobInfo();
        try {
            ArrayList<JobInfo> jobInfos = JDBCUtils.getInstance().executeQuery(sql, parameters, new JobInfo().getMapping(), JobInfo.class, closeDB);
            if(!jobInfos.isEmpty()){
                jobInfo = jobInfos.get(0);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return jobInfo;
    }
}
